--    Program: 		CAPPOEXT_PERSON.sql
--    Description:	Extract PO Person (Employee) information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
   
   l_set_of_books_name		varchar2(20);
   l_set_of_books_id        number;
                    		  
   cursor cur_po_persons(p_sob_name varchar2) is
      select pap.* 
      from   per_all_people_f pap,
      	     hr_organization_units hou
      where  pap.business_group_id = hou.business_group_id
      and    upper(hou.name) = 'BG_'||upper(substr(p_sob_name, -4));

begin
   l_pathname := '/tmp';
   l_filename := 'CAPPO_PEROSN.dat';
   l_utl_file := utl_file.fopen(l_pathname,l_filename,'w',32767);
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_set_of_books_name := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_set_of_books_name);  
   exception
      when others then
        dbms_output.put_line(l_set_of_books_name||': set of books name not found');
   end; 

   for cur in cur_po_persons(l_set_of_books_name) loop   	
    
	 -- output data
     utl_file.put_line( l_utl_file, 
       cur.PERSON_ID                      || chr(9) ||
       cur.EFFECTIVE_START_DATE           || chr(9) ||
       cur.EFFECTIVE_END_DATE             || chr(9) ||
       cur.BUSINESS_GROUP_ID              || chr(9) ||
       cur.PERSON_TYPE_ID                 || chr(9) ||
       cur.LAST_NAME                      || chr(9) ||
       cur.START_DATE                     || chr(9) ||
       cur.APPLICANT_NUMBER               || chr(9) ||
       cur.COMMENT_ID                     || chr(9) ||
       cur.CURRENT_APPLICANT_FLAG         || chr(9) ||
       cur.CURRENT_EMP_OR_APL_FLAG        || chr(9) ||
       cur.CURRENT_EMPLOYEE_FLAG          || chr(9) ||
       cur.DATE_EMPLOYEE_DATA_VERIFIED    || chr(9) ||
       cur.DATE_OF_BIRTH                  || chr(9) ||
       cur.EMAIL_ADDRESS                  || chr(9) ||
       cur.EMPLOYEE_NUMBER                || chr(9) ||
       cur.EXPENSE_CHECK_SEND_TO_ADDRESS  || chr(9) ||
       cur.FIRST_NAME                     || chr(9) ||
       cur.FULL_NAME                      || chr(9) ||
       cur.KNOWN_AS                       || chr(9) ||
       cur.MARITAL_STATUS                 || chr(9) ||
       cur.MIDDLE_NAMES                   || chr(9) ||
       cur.NATIONALITY                    || chr(9) ||
       cur.NATIONAL_IDENTIFIER            || chr(9) ||
       cur.PREVIOUS_LAST_NAME             || chr(9) ||
       cur.REGISTERED_DISABLED_FLAG       || chr(9) ||
       cur.SEX                            || chr(9) ||
       cur.TITLE                          || chr(9) ||
       cur.VENDOR_ID                      || chr(9) ||
       cur.WORK_TELEPHONE                 || chr(9) ||
       cur.REQUEST_ID                     || chr(9) ||
       cur.PROGRAM_APPLICATION_ID         || chr(9) ||
       cur.PROGRAM_ID                     || chr(9) ||
       cur.PROGRAM_UPDATE_DATE            || chr(9) ||
       cur.ATTRIBUTE_CATEGORY             || chr(9) ||
       cur.ATTRIBUTE1                     || chr(9) ||
       cur.ATTRIBUTE2                     || chr(9) ||
       cur.ATTRIBUTE3                     || chr(9) ||
       cur.ATTRIBUTE4                     || chr(9) ||
       cur.ATTRIBUTE5                     || chr(9) ||
       cur.ATTRIBUTE6                     || chr(9) ||
       cur.ATTRIBUTE7                     || chr(9) ||
       cur.ATTRIBUTE8                     || chr(9) ||
       cur.ATTRIBUTE9                     || chr(9) ||
       cur.ATTRIBUTE10                    || chr(9) ||
       cur.ATTRIBUTE11                    || chr(9) ||
       cur.ATTRIBUTE12                    || chr(9) ||
       cur.ATTRIBUTE13                    || chr(9) ||
       cur.ATTRIBUTE14                    || chr(9) ||
       cur.ATTRIBUTE15                    || chr(9) ||
       cur.ATTRIBUTE16                    || chr(9) ||
       cur.ATTRIBUTE17                    || chr(9) ||
       cur.ATTRIBUTE18                    || chr(9) ||
       cur.ATTRIBUTE19                    || chr(9) ||
       cur.ATTRIBUTE20                    || chr(9) ||
       cur.ATTRIBUTE21                    || chr(9) ||
       cur.ATTRIBUTE22                    || chr(9) ||
       cur.ATTRIBUTE23                    || chr(9) ||
       cur.ATTRIBUTE24                    || chr(9) ||
       cur.ATTRIBUTE25                    || chr(9) ||
       cur.ATTRIBUTE26                    || chr(9) ||
       cur.ATTRIBUTE27                    || chr(9) ||
       cur.ATTRIBUTE28                    || chr(9) ||
       cur.ATTRIBUTE29                    || chr(9) ||
       cur.ATTRIBUTE30                    || chr(9) ||
       cur.LAST_UPDATE_DATE               || chr(9) ||
       cur.LAST_UPDATED_BY                || chr(9) ||
       cur.LAST_UPDATE_LOGIN              || chr(9) ||
       cur.CREATED_BY                     || chr(9) ||
       cur.CREATION_DATE                  || chr(9) ||
       cur.PER_INFORMATION_CATEGORY       || chr(9) ||
       cur.PER_INFORMATION1               || chr(9) ||
       cur.PER_INFORMATION2               || chr(9) ||
       cur.PER_INFORMATION3               || chr(9) ||
       cur.PER_INFORMATION4               || chr(9) ||
       cur.PER_INFORMATION5               || chr(9) ||
       cur.PER_INFORMATION6               || chr(9) ||
       cur.PER_INFORMATION7               || chr(9) ||
       cur.PER_INFORMATION8               || chr(9) ||
       cur.PER_INFORMATION9               || chr(9) ||
       cur.PER_INFORMATION10              || chr(9) ||
       cur.PER_INFORMATION11              || chr(9) ||
       cur.PER_INFORMATION12              || chr(9) ||
       cur.PER_INFORMATION13              || chr(9) ||
       cur.PER_INFORMATION14              || chr(9) ||
       cur.PER_INFORMATION15              || chr(9) ||
       cur.PER_INFORMATION16              || chr(9) ||
       cur.PER_INFORMATION17              || chr(9) ||
       cur.PER_INFORMATION18              || chr(9) ||
       cur.PER_INFORMATION19              || chr(9) ||
       cur.PER_INFORMATION20              || chr(9) ||
       cur.BACKGROUND_CHECK_STATUS        || chr(9) ||
       cur.BACKGROUND_DATE_CHECK          || chr(9) ||
       cur.BLOOD_TYPE                     || chr(9) ||
       cur.CORRESPONDENCE_LANGUAGE        || chr(9) ||
       cur.FAST_PATH_EMPLOYEE             || chr(9) ||
       cur.FTE_CAPACITY                   || chr(9) ||
       cur.HOLD_APPLICANT_DATE_UNTIL      || chr(9) ||
       cur.HONORS                         || chr(9) ||
       cur.INTERNAL_LOCATION              || chr(9) ||
       cur.LAST_MEDICAL_TEST_BY           || chr(9) ||
       cur.LAST_MEDICAL_TEST_DATE         || chr(9) ||
       cur.MAILSTOP                       || chr(9) ||
       cur.OFFICE_NUMBER                  || chr(9) ||
       cur.ON_MILITARY_SERVICE            || chr(9) ||
       cur.ORDER_NAME                     || chr(9) ||
       cur.PRE_NAME_ADJUNCT               || chr(9) ||
       cur.PROJECTED_START_DATE           || chr(9) ||
       cur.REHIRE_AUTHORIZOR              || chr(9) ||
       cur.REHIRE_RECOMMENDATION          || chr(9) ||
       cur.RESUME_EXISTS                  || chr(9) ||
       cur.RESUME_LAST_UPDATED            || chr(9) ||
       cur.SECOND_PASSPORT_EXISTS         || chr(9) ||
       cur.STUDENT_STATUS                 || chr(9) ||
       cur.SUFFIX                         || chr(9) ||
       cur.WORK_SCHEDULE                  || chr(9) ||
       cur.PER_INFORMATION21              || chr(9) ||
       cur.PER_INFORMATION22              || chr(9) ||
       cur.PER_INFORMATION23              || chr(9) ||
       cur.PER_INFORMATION24              || chr(9) ||
       cur.PER_INFORMATION25              || chr(9) ||
       cur.PER_INFORMATION26              || chr(9) ||
       cur.PER_INFORMATION27              || chr(9) ||
       cur.PER_INFORMATION28              || chr(9) ||
       cur.PER_INFORMATION29              || chr(9) ||
       cur.PER_INFORMATION30              || chr(9) ||
       cur.OBJECT_VERSION_NUMBER          || chr(9) ||
       cur.DATE_OF_DEATH                  || chr(9) ||
       cur.REHIRE_REASON                  || chr(9) ||
       cur.COORD_BEN_MED_PLN_NO           || chr(9) ||
       cur.COORD_BEN_NO_CVG_FLAG          || chr(9) ||
       cur.DPDNT_ADOPTION_DATE            || chr(9) ||
       cur.DPDNT_VLNTRY_SVCE_FLAG         || chr(9) ||
       cur.RECEIPT_OF_DEATH_CERT_DATE     || chr(9) ||
       cur.USES_TOBACCO_FLAG              || chr(9) ||
       cur.BENEFIT_GROUP_ID               || chr(9) ||
       cur.ORIGINAL_DATE_OF_HIRE          || chr(9) ||
       cur.TOWN_OF_BIRTH                  || chr(9) ||
       cur.REGION_OF_BIRTH                || chr(9) ||
       cur.COUNTRY_OF_BIRTH               || chr(9) ||
       cur.GLOBAL_PERSON_ID               || chr(9) ||
       cur.COORD_BEN_MED_PL_NAME          || chr(9) ||
       cur.COORD_BEN_MED_INSR_CRR_NAME    || chr(9) ||
       cur.COORD_BEN_MED_INSR_CRR_IDENT   || chr(9) ||
       cur.COORD_BEN_MED_EXT_ER           || chr(9) ||
       cur.COORD_BEN_MED_CVG_STRT_DT      || chr(9) ||
       cur.COORD_BEN_MED_CVG_END_DT       || chr(9) ||
       cur.PARTY_ID                       || chr(9) ||
       cur.NPW_NUMBER                     || chr(9) ||
       cur.CURRENT_NPW_FLAG 
	 );
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := 'Po Person not exists';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ( 'CAPPOEXT_PERSON', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg ); 
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
